package com.fau.wm.service.impl;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;
import java.util.Set;

import javax.validation.ConstraintViolation;

import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.apache.struts2.ServletActionContext;
import org.springframework.security.core.context.SecurityContextHolder;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Propagation;
import org.springframework.transaction.annotation.Transactional;

import com.fau.wm.entity.MedicalAdmin;
import com.fau.wm.entity.MedicalInStock;
import com.fau.wm.entity.MedicalInventory;
import com.fau.wm.entity.MedicalInventoryDetail;
import com.fau.wm.entity.MedicalKeyValue;
import com.fau.wm.entity.MedicalMedicine;
import com.fau.wm.entity.MedicalWarehouse;
import com.fau.wm.service.IInventoryManagerService;
import com.fau.wm.util.Page;
import com.fau.wm.util.ParamUtil;
import com.fau.wm.util.SystemConfig;
import com.fau.wm.util.ValidatorUtil;

@Service
@Transactional(readOnly = true)
public class InventoryManagerService extends BaseService implements
		IInventoryManagerService {

	@Override
	public Page findInventoryByPage(MedicalInventory inventory,
			Map<String, Object> map) {
		StringBuilder countSql = new StringBuilder("select count(*) from MedicalInventory a where a.hospitalId = ? and a.status in(?,?,?) ");
		StringBuilder statusSql = new StringBuilder("from MedicalInventory a where a.hospitalId = ? and a.status in(?,?,?) ");

		List<Object> objectList = new ArrayList<Object>();
		objectList.add(inventory.getHospitalId());
		objectList.add(SystemConfig.INVENTOR_STATUS_APPROVAL);
		objectList.add(SystemConfig.INVENTOR_STATUS_REJECT);
		objectList.add(SystemConfig.INVENTOR_STATUS_NEW);

		if (StringUtils.isNotBlank(inventory.getTitle())) {
			countSql.append("and a.title like ? ");
			statusSql.append("and a.title like ? ");
			objectList.add("%" + inventory.getTitle() + "%");
		}

		if (StringUtils.isNotBlank(inventory.getSerialNum())) {
			countSql.append("and a.serialNum = ? ");
			statusSql.append("and a.serialNum = ? ");
			objectList.add(inventory.getSerialNum());
		}

		if (StringUtils.isNotBlank(inventory.getWarehouseName())) {
			countSql.append("and a.warehouse.name like ? ");
			statusSql.append("and a.warehouse.name like ? ");
			objectList.add("%"+inventory.getWarehouseName()+"%");
		}
		
		// 排序
		String[] sortField = (String[]) map.get("sortField");
		String[] sortOrder = (String[]) map.get("sortOrder");
		if(StringUtils.isNotBlank(sortField[0]) && StringUtils.isNotBlank(sortOrder[0])) {
			statusSql.append("order by a." + sortField[0] + " " + sortOrder[0]);
		} else {
			statusSql.append("order by a.createTime desc ");
		}	
		
		Page page = this.iBaseDao.getPage(
				ParamUtil.getInteger(map, "pageIndex", 1),
				ParamUtil.getInteger(map, "pageSize", 20), map,
				countSql.toString(), statusSql.toString(), objectList);

		return page;
	}

	@Override
	@Transactional(readOnly = false, propagation = Propagation.REQUIRED, rollbackFor = Exception.class)
	public boolean saveInventory(MedicalInventory inventory,
			MedicalAdmin writeUser) {
		inventory.setWriteUser(writeUser);
		MedicalWarehouse warehouse = (MedicalWarehouse) this.iBaseDao
				.getObject(MedicalWarehouse.class, inventory.getWarehouseId());
		inventory.setWarehouse(warehouse);
		inventory.setHospitalId(writeUser.getEnterpriseId());
		Set<ConstraintViolation<Object>> errorInfo = ValidatorUtil
				.validateObj(inventory);
		if (errorInfo.size() > 0) {
			return false;
		} else {
			return this.iBaseDao.saveObject(inventory);
		}
	}

	@Override
	@Transactional
	public Boolean verSerialNum(String serialNum, String hospitalId) {
		MedicalInventory inventory = (MedicalInventory) this.iBaseDao.getObjectByHql("from MedicalInventory a where a.serialNum=? and a.hospitalId = ?", serialNum, hospitalId);
		if (null == inventory) {
			return true;
		} else {
			return false;
		}
	}

	@Override
	@Transactional(readOnly = false, propagation = Propagation.REQUIRED, rollbackFor = Exception.class)
	public void saveEdit(MedicalInventory inventory) {
		MedicalInventory bean = (MedicalInventory) this.iBaseDao.getObject(MedicalInventory.class, inventory.getId());
		MedicalWarehouse warehouse = (MedicalWarehouse) this.iBaseDao.getObject(MedicalWarehouse.class, inventory.getWarehouseId());
		bean.setWarehouse(warehouse);
		bean.setSerialNum(inventory.getSerialNum());
		bean.setTitle(inventory.getTitle());
		bean.setInventoryTime(inventory.getInventoryTime());
		bean.setUpdateTime(new Date());
		this.iBaseDao.saveObject(bean);
	}

	@Override
	@Transactional(readOnly = false, propagation = Propagation.REQUIRED, rollbackFor = Exception.class)
	public void delInventory(MedicalInventory inventory) {
		String[] ids = inventory.getId().split("-");
		for (String id : ids) {
			this.iBaseDao.updateHql("delete from MedicalInventoryDetail a where a.inventory.id=? ", id);
			this.iBaseDao.updateHql("delete from MedicalInventory a where a.id=? ", id);
		}
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<MedicalInventoryDetail> findInventoryDetailList(String inventoryId) {
		List<MedicalInventoryDetail> list = this.iBaseDao.getBySql("from MedicalInventoryDetail a where a.inventory.id=? ", null, inventoryId);
		MedicalAdmin user = (MedicalAdmin) SecurityContextHolder.getContext().getAuthentication().getPrincipal();
		for (MedicalInventoryDetail medicalInventoryDetail : list) {
			MedicalKeyValue kv = this.iBaseDao.getValueByKey(user.getEnterpriseId(), medicalInventoryDetail.getMedicine().getUnit());
			medicalInventoryDetail.getMedicine().setUnitValue(kv.getKeyValue());
		}
		return list;
	}
	
	@Override
	public Page findMedicineHistoryInventory(Map<String, Object> map,String mid) {
		StringBuilder countSql = new StringBuilder("select count(*) from MedicalInventoryDetail a where a.inventory.hospitalId=? and a.medicine.id=? and a.inventory.status=? ");
		StringBuilder statusSql = new StringBuilder("from MedicalInventoryDetail a where a.inventory.hospitalId=? and a.medicine.id=? and a.inventory.status=? ");
		
		MedicalAdmin user = (MedicalAdmin) SecurityContextHolder.getContext().getAuthentication().getPrincipal();
		List<Object> objectList = new ArrayList<Object>();
		objectList.add(user.getEnterpriseId());
		objectList.add(mid);
		objectList.add(SystemConfig.INVENTOR_STATUS_APPROVAL);
		
		statusSql.append("order by a.createTime desc ");
		
		Page page = this.iBaseDao.getPage(ParamUtil.getInteger(map,"pageIndex",1),ParamUtil.getInteger(map, "pageSize", 20),
				map, countSql.toString(), statusSql.toString(), objectList);
		@SuppressWarnings("unchecked")
		List<MedicalInventoryDetail> list = (List<MedicalInventoryDetail>) page.getResult();
		for (MedicalInventoryDetail inventoryDetail : list) {
			MedicalKeyValue keyValue = this.iBaseDao.getValueByKey(user.getEnterpriseId(), inventoryDetail.getMedicine().getUnit());
			if(null != keyValue){
				inventoryDetail.getMedicine().setUnitValue(keyValue.getKeyValue());
			}
		}
		return page;
	}

	@Override
	@Transactional(readOnly = false, propagation = Propagation.REQUIRED, rollbackFor = Exception.class)
	public boolean saveInventoryDetail(MedicalInventoryDetail inventoryDetail) {
		MedicalMedicine medicine = (MedicalMedicine) this.iBaseDao.getObject(
				MedicalMedicine.class, inventoryDetail.getMedicineId());
		inventoryDetail.setMedicine(medicine);
		MedicalInventory inventory = (MedicalInventory) this.iBaseDao.getObject(MedicalInventory.class, inventoryDetail.getInventoryId());
		inventoryDetail.setInventory(inventory);
		// 获得系统中药品数量
		int sysNum = 0;
		MedicalInStock inStock = (MedicalInStock) this.iBaseDao.getObjectByHql("from MedicalInStock a where a.warehouse.id=? and a.medicine.id=? ", inventoryDetail.getWarehouseId(), inventoryDetail.getMedicineId());
		if(null != inStock) {
			sysNum = inStock.getQuantity();
		}
		inventoryDetail.setSysNum(sysNum);
		
		int warNum = inventoryDetail.getWarNum();
		inventoryDetail.setQuantity(warNum-sysNum);
		return this.iBaseDao.saveObject(inventoryDetail);
	}

	@Override
	@Transactional(readOnly = false, propagation = Propagation.REQUIRED, rollbackFor = Exception.class)
	public void saveEditInventoryDetail(MedicalInventoryDetail inventoryDetail) {
		MedicalInventoryDetail bean = (MedicalInventoryDetail) this.iBaseDao.getObject(MedicalInventoryDetail.class,inventoryDetail.getId());
		MedicalMedicine medicine = (MedicalMedicine) this.iBaseDao.getObject(MedicalMedicine.class, inventoryDetail.getMedicineId());
		bean.setMedicine(medicine);
		// 获得系统中药品数量
		MedicalInStock inStock = (MedicalInStock) this.iBaseDao.getObjectByHql("from MedicalInStock a where a.warehouse.id=? and a.medicine.id=? ", inventoryDetail.getWarehouseId(),inventoryDetail.getMedicineId());
		int sysNum = inStock.getQuantity();
		bean.setSysNum(sysNum);
		int warNum = inventoryDetail.getWarNum();
		bean.setQuantity(warNum-sysNum);
		bean.setWarNum(inventoryDetail.getWarNum());
		this.iBaseDao.saveObject(bean);
	}

	@Override
	@Transactional(readOnly = false, propagation = Propagation.REQUIRED, rollbackFor = Exception.class)
	public void delInventoryDetail(MedicalInventoryDetail inventoryDetail) {
		this.iBaseDao.deleteObject(inventoryDetail);
	}

	@Override
	public Boolean checkMedicineExist(String inventoryId, String medicineId) {
		MedicalInventoryDetail inventoryDetail = (MedicalInventoryDetail) this.iBaseDao
				.getObjectByHql(
						"from MedicalInventoryDetail a where a.inventory.id=? and a.medicine.id=? ",
						inventoryId, medicineId);
		if (null == inventoryDetail) {
			return true;
		} else {
			return false;
		}
	}

	@Override
	public Page findInventoryAuditByPage(MedicalInventory inventory,
			Map<String, Object> map) {
		StringBuilder countSql = new StringBuilder("select count(*) from MedicalInventory a where a.hospitalId = ? ");
		StringBuilder statusSql = new StringBuilder("from MedicalInventory a where a.hospitalId = ? ");

		List<Object> objectList = new ArrayList<Object>();
		objectList.add(inventory.getHospitalId());

		if (StringUtils.isNotBlank(inventory.getTitle())) {
			countSql.append("and a.title like ? ");
			statusSql.append("and a.title like ? ");
			objectList.add("%" + inventory.getTitle() + "%");
		}

		if (StringUtils.isNotBlank(inventory.getSerialNum())) {
			countSql.append("and a.serialNum = ? ");
			statusSql.append("and a.serialNum = ? ");
			objectList.add(inventory.getSerialNum());
		}

		if (StringUtils.isNotBlank(inventory.getWarehouseName())) {
			countSql.append("and a.warehouse.name like ? ");
			statusSql.append("and a.warehouse.name like ? ");
			objectList.add("%" + inventory.getWarehouseName() + "%");
		}

		countSql.append("and a.status=? ");
		statusSql.append("and a.status=? ");
		objectList.add(SystemConfig.INVENTOR_STATUS_PENDING);
		
		// 排序
		String[] sortField = (String[]) map.get("sortField");
		String[] sortOrder = (String[]) map.get("sortOrder");
		if(StringUtils.isNotBlank(sortField[0]) && StringUtils.isNotBlank(sortOrder[0])) {
			statusSql.append("order by a." + sortField[0] + " " + sortOrder[0]);
		} else {
			statusSql.append("order by a.createTime desc ");
		}	
		
		Page page = this.iBaseDao.getPage(
				ParamUtil.getInteger(map, "pageIndex", 1),
				ParamUtil.getInteger(map, "pageSize", 20), map,
				countSql.toString(), statusSql.toString(), objectList);

		return page;
	}

	@Override
	@Transactional(readOnly = false, propagation = Propagation.REQUIRED, rollbackFor = Exception.class)
	public void updateAuditUser(MedicalAdmin auditUser, String inventoryId) {
		MedicalInventory inventory = (MedicalInventory) this.iBaseDao
				.getObject(MedicalInventory.class, inventoryId);
		inventory.setAuditUser(auditUser);
	}

	@Override
	@Transactional(readOnly = false, propagation = Propagation.REQUIRED, rollbackFor = Exception.class)
	public void updateinventoryStatus(String status, String inventoryId) {
		MedicalInventory inventory = (MedicalInventory) this.iBaseDao
				.getObject(MedicalInventory.class, inventoryId);
		inventory.setStatus(status);
	}

	@SuppressWarnings("unchecked")
	@Override
	public Workbook exportExcelAll(MedicalAdmin admin, String wid)
			throws Exception {
		String realPath = ServletActionContext.getServletContext().getRealPath(SystemConfig.INVENTORY_EXPORT);
		File file = new File(realPath);
		if(!file.exists()){
			throw new FileNotFoundException("没有找到excel文件");
		}
		
		InputStream is = new FileInputStream(file);
		Workbook workbook = WorkbookFactory.create(is);
		is.close();
		Sheet sheet = workbook.getSheetAt(0);
		
		MedicalWarehouse warehouse = (MedicalWarehouse) this.iBaseDao.getObject(MedicalWarehouse.class, wid);
		
		Row row = sheet.getRow(1);
		row.createCell(1).setCellValue(warehouse.getName());
		
		List<MedicalInStock> list = this.iBaseDao.getBySql("from MedicalInStock a where a.hospitalId=? and a.warehouse.id=? ", null, admin.getEnterpriseId(),wid);
		int size = list.size();
		for(int i=0;i<size;i++){
			MedicalInStock inStock = list.get(i);
			Row currentRow = sheet.createRow(i+3);
			currentRow.createCell(0).setCellValue(inStock.getMedicine().getSerialNum());
			currentRow.createCell(1).setCellValue(inStock.getMedicine().getName());
			MedicalKeyValue kv = this.iBaseDao.getValueByKey(admin.getEnterpriseId(), inStock.getMedicine().getUnit());
			currentRow.createCell(2).setCellValue(kv.getKeyValue());
			currentRow.createCell(3).setCellValue(inStock.getQuantity());
		}
		return workbook;
	}

	@Override
	@Transactional(readOnly = false, propagation = Propagation.REQUIRED, rollbackFor = Exception.class)
	public String importInventory(MedicalAdmin admin,String iid, File file)throws Exception {
		// 该盘点单下的清空盘点详细表
		this.iBaseDao.updateHql("delete from MedicalInventoryDetail a where a.inventory.id=? ", iid);
		
		InputStream is = new FileInputStream(file);
		Workbook workbook = WorkbookFactory.create(is);
		is.close();
		Sheet sheet = workbook.getSheetAt(0);
		for (Row row : sheet) {
			// 跳过表头
			if(row.getRowNum() < 3){
				continue;
			}
			
			if(null == row.getCell(0)){
				break;
			}
			
			MedicalInventoryDetail inventoryDetail = new MedicalInventoryDetail();
			MedicalInventory inventory = (MedicalInventory) this.iBaseDao.getObject(MedicalInventory.class, iid);
			inventoryDetail.setInventory(inventory);
			
			String serialNum = null;
			if(HSSFCell.CELL_TYPE_STRING == row.getCell(0).getCellType()){
				serialNum = row.getCell(0).getStringCellValue().trim(); //药品编号     
			}else{
				serialNum = String.format("%.0f",row.getCell(0).getNumericCellValue());
			}
			
			MedicalMedicine medicine = (MedicalMedicine) this.iBaseDao.getObjectByHql("from MedicalMedicine a where a.hospitalId=? and a.serialNum=? ", admin.getEnterpriseId(),serialNum);
			if(null == medicine){
				return "药品不存在";
			}else{
				inventoryDetail.setMedicine(medicine);
			}
			
			String sysNum = null;
			if(null == row.getCell(3)){
				return "系统数量不能为空";
			}else{
				if(HSSFCell.CELL_TYPE_STRING == row.getCell(3).getCellType()){
					sysNum = row.getCell(3).getStringCellValue().trim(); //系统数量
				}else{
					sysNum = String.format("%.0f",row.getCell(3).getNumericCellValue());
				}
				inventoryDetail.setSysNum(Integer.valueOf(sysNum));
			}
			
			String warNum = null;
			if(null == row.getCell(4)){
				return "仓库数量不能为空";
			}else{
				if(HSSFCell.CELL_TYPE_STRING == row.getCell(4).getCellType()){
					warNum = row.getCell(4).getStringCellValue().trim(); //仓库数量
				}else{
					warNum = String.format("%.0f",row.getCell(4).getNumericCellValue());
				}
				inventoryDetail.setWarNum(Integer.valueOf(warNum));
			}
			
			inventoryDetail.setQuantity(Integer.valueOf(warNum)-Integer.valueOf(sysNum));
			this.iBaseDao.saveObject(inventoryDetail);
		}
		return null;
	}
	
	@SuppressWarnings("unchecked")
	@Override
	@Transactional(readOnly = false, propagation = Propagation.REQUIRED, rollbackFor = Exception.class)
	public boolean toAudit(String sid) {
		List<MedicalInventoryDetail> list = this.iBaseDao.getBySql("from MedicalInventoryDetail a where a.inventory.id=? ", null, sid);
		if(list.size() < 1){
			return false;
		}else{
			this.iBaseDao.updateHql("update MedicalInventory a set a.status=? where a.id=? ", SystemConfig.INVENTOR_STATUS_PENDING,sid);
			return true;
		}
	}

	@Override
	public boolean validProExist(MedicalInventoryDetail inventoryDetail) {
		MedicalInStock inStock = (MedicalInStock) this.iBaseDao.getObjectByHql("from MedicalInStock a where a.medicine.id=? and a.warehouse.id=? ", inventoryDetail.getMedicineId(), inventoryDetail.getWarehouseId());
		if(null != inStock) {
			return true;
		} else {
			return false;
		}
	}

	@Override
	public String inventoryValidPro(MedicalInventoryDetail inventoryDetail) {
		StringBuilder errorInfo = new StringBuilder("");
		if(inventoryDetail.getId().indexOf(",") != -1) {
			String[] detailIds = inventoryDetail.getId().split(",");
			for(String dId : detailIds) {
				MedicalInventoryDetail bean = (MedicalInventoryDetail) this.iBaseDao.getObject(MedicalInventoryDetail.class, dId);
				MedicalInStock inStock = (MedicalInStock) this.iBaseDao.getObjectByHql("from MedicalInStock a where a.warehouse.id=? and a.medicine.id=? ", inventoryDetail.getWarehouseId(), bean.getMedicine().getId());
				if(null != inStock) {
					continue;
				} else {
					errorInfo.append(bean.getMedicine().getName()+"、");
				}
			}
			if(StringUtils.isNotBlank(errorInfo.toString())) {
				errorInfo.deleteCharAt(errorInfo.lastIndexOf("、"));
			} else {
				errorInfo.append("true");
			}
		} else {
			MedicalInventoryDetail bean = (MedicalInventoryDetail) this.iBaseDao.getObject(MedicalInventoryDetail.class, inventoryDetail.getId());
			MedicalInStock inStock = (MedicalInStock) this.iBaseDao.getObjectByHql("from MedicalInStock a where a.warehouse.id=? and a.medicine.id=? ", inventoryDetail.getWarehouseId(), bean.getMedicine().getId());
			if(null != inStock) {
				errorInfo.append("true");
			} else {
				errorInfo.append(bean.getMedicine().getName());
			}
		}
		return errorInfo.toString();
	}
	
	
	

}
